package com.jdbc.advanced;

import com.jdbc.advanced.pojo.Employee;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;

public class JDBCAdvanced {

    @Test
    public void testORM() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_emp where emp_id = ?");
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

        Employee employee = null;
        while (resultSet.next()) {
            employee = new Employee();
            employee.setEmpId(resultSet.getInt("emp_id"));
            employee.setEmpName(resultSet.getString("emp_name"));
            employee.setSalary(resultSet.getDouble("emp_salary"));
            employee.setEmpAge(resultSet.getInt("emp_age"));
        }
        System.out.println(employee);

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testORMList() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_emp");
        ResultSet resultSet = preparedStatement.executeQuery();

        Employee employee = null;
        ArrayList<Employee> employees = new ArrayList<>();

        while (resultSet.next()) {
            employee = new Employee();
            employee.setEmpId(resultSet.getInt("emp_id"));
            employee.setEmpName(resultSet.getString("emp_name"));
            employee.setSalary(resultSet.getDouble("emp_salary"));
            employee.setEmpAge(resultSet.getInt("emp_age"));
            employees.add(employee);
        }

        for (Employee empResult : employees) {
            System.out.println(empResult);
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testORMReturnPrimaryKey() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_emp(emp_name, emp_salary, emp_age) values (?,?,?)", Statement.RETURN_GENERATED_KEYS);

        preparedStatement.setString(1, "mike");
        preparedStatement.setDouble(2, 500);
        preparedStatement.setInt(3, 20);
        Employee employee = new Employee(null, "mike", 500.45, 20);

        int result = preparedStatement.executeUpdate();
        ResultSet generatedKeys = null;

        if (result == 1) {
            generatedKeys = preparedStatement.getGeneratedKeys();
            while (generatedKeys.next()) {
                employee.setEmpId(generatedKeys.getInt(1));
            }
            System.out.println("插入成功！");
        } else {
            System.out.println("插入失败！");
        }
        System.out.println(employee);

        if (generatedKeys != null) {
            generatedKeys.close();
        }
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testMoreInsert() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/jdbc?rewriteBatchedStatement=true", "root", "1234");
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_emp(emp_name, emp_salary, emp_age) values (?,?,?)");

        for (int i = 0; i < 1000; i++) {
            preparedStatement.setString(1, "mike" + i);
            preparedStatement.setDouble(2, 500 + i);
            preparedStatement.setInt(3, 20 + i);

            preparedStatement.addBatch();
        }

        preparedStatement.executeBatch();

        preparedStatement.close();
        connection.close();

    }
}
